package com.example.demo.service;
import com.example.demo.entity.User;
import com.example.demo.mapper.UserMapper;
import jakarta.servlet.http.HttpServletResponse;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;

import java.util.List;

@Service
public class UserService {

    @Autowired
    private UserMapper userMapper;

    public void importExcel(MultipartFile file) throws IOException {
        try (InputStream inputStream = file.getInputStream();
             Workbook workbook = new XSSFWorkbook(inputStream)) {

            Sheet sheet = workbook.getSheetAt(0);
            for (Row row : sheet) {
                if (row.getRowNum() == 0) {
                    // Skip header row
                    continue;
                }
                User user = new User();
                user.setId(getCellValue(row.getCell(0)));
                user.setName(getCellValue(row.getCell(1)));
                user.setPassword(getCellValue(row.getCell(2)));
                user.setEmail(getCellValue(row.getCell(3)));
                userMapper.insertUser(user);
            }
        }
    }

    private String getCellValue(Cell cell) {
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    return String.valueOf(cell.getNumericCellValue());
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            case BLANK:
                return "";
            default:
                return "";
        }
    }
    public void exportToExcel(HttpServletResponse response) throws IOException {
        List<User> users = userMapper.getAllUsers();

        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("Users");
        Row headerRow1 = sheet.createRow(0);

        // 创建单元格样式用于合并单元格、居中、加粗
        CellStyle headerCellStyle1 = workbook.createCellStyle();
        // 设置水平和垂直对齐方式为居中
        headerCellStyle1.setAlignment(HorizontalAlignment.CENTER);
        headerCellStyle1.setVerticalAlignment(VerticalAlignment.CENTER);
        // 创建字体并设置为加粗
        Font headerFont1 = workbook.createFont();
        headerFont1.setBold(true);
        headerCellStyle1.setFont(headerFont1);

        // 创建合并后的单元格
        Cell mergedCell = headerRow1.createCell(0);
        mergedCell.setCellValue("用户表");
        // 合并第一行的四个单元格（假设索引为0 - 3）
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 5));
        mergedCell.setCellStyle(headerCellStyle1);
        // Create header row
        Row headerRow = sheet.createRow(1);
        headerRow.createCell(0).setCellValue("ID");
        headerRow.createCell(1).setCellValue("Name");
        headerRow.createCell(2).setCellValue("Email");
        headerRow.createCell(3).setCellValue("语文");
        headerRow.createCell(4).setCellValue("英语");
        headerRow.createCell(5).setCellValue("平均数");

        // 创建字体样式
        Font headerFont = workbook.createFont();
        headerFont.setBold(true);
        headerFont.setFontHeightInPoints((short) 14);
        headerFont.setColor(IndexedColors.BLACK.getIndex());

        // 创建单元格样式用于设置标题背景色
        CellStyle headerCellStyle = workbook.createCellStyle();
        // 设置背景填充颜色为灰色
        headerCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        headerCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        for (int i = 0; i < 6; i++) {
            Cell cell = headerRow.getCell(i);
            if (cell == null) {
                cell = headerRow.createCell(i);
            }
            cell.setCellStyle(headerCellStyle);
        }
        // 创建数据行样式
        CellStyle dataCellStyle = workbook.createCellStyle();
        // 设置边框样式
        dataCellStyle.setBorderTop(BorderStyle.THIN);
        dataCellStyle.setBorderBottom(BorderStyle.THIN);
        dataCellStyle.setBorderLeft(BorderStyle.THIN);
        dataCellStyle.setBorderRight(BorderStyle.THIN);

        // Create data rows
        int rowNum = 2;
        for (User user : users) {
            Row row = sheet.createRow(rowNum++);
            Cell cell0 = row.createCell(0);
            cell0.setCellValue(user.getId());
            cell0.setCellStyle(dataCellStyle);
            Cell cell1 = row.createCell(1);
            cell1.setCellValue(user.getName());
            cell1.setCellStyle(dataCellStyle);
            Cell cell2 = row.createCell(2);
            cell2.setCellValue(user.getEmail());
            cell2.setCellStyle(dataCellStyle);
            Cell cell3 = row.createCell(3);
            cell3.setCellValue(user.getScore());
            cell3.setCellStyle(dataCellStyle);
            Cell cell4 = row.createCell(4);
            cell4.setCellValue(user.getEnglish());
            cell4.setCellStyle(dataCellStyle);
            int cellNum = 0;
            for (User user2 : users) {
                cellNum=(user2.getScore()+user2.getEnglish())/2;
            }
            Cell cell5 = row.createCell(5);
            cell5.setCellValue(cellNum);
            cell5.setCellStyle(dataCellStyle);
//            row.createCell(5).setCellStyle(dataCellStyle);
//            row.createCell(5).setCellValue(cellNum);
        }

        // Set response headers
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setHeader("Content-Disposition", "attachment; filename=users.xlsx");

        // Write Excel file to response output stream
        workbook.write(response.getOutputStream());
        workbook.close();
    }
}
